capture log close
clear all
set maxvar 30000
set more off
pause off
# delimit;

local crime "";
local output "";

/* 
Table B12
RD and IV estimates of criminal justice outcomes (by unemployment rate)
*/


/* ************************* */
/* **** CRIME VARIABLES **** */
/* ************************* */

use "`crime'/prwora_crime_summstats2_exactage.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_uealllow "ual" ;
local pre_ueallhigh "uah" ;

foreach sample in uealllow ueallhigh  {;

	preserve ;

		keep	`pre_`sample''n_crime2char* `pre_`sample''n_crime2genr* 
				`pre_`sample''n_crime2ahinc* `pre_`sample''n_crime2shinc* 
				;

		xpose, clear varname ;

		rename v1 mean ;
		rename _varname variables ;
		order variables mean ;

		replace variables = subinstr(variables,"_18_38", "", .) ;
		replace variables = subinstr(variables,"`pre_`sample''n_crime2", "", .) ;
		replace variables = subinstr(variables,"`pre_`sample''n_", "", .) ;
		replace variables = "age18_yes" if variables == "`pre_`sample''age18_yes" ; 

		tempfile `sample'_mean ;
		save ``sample'_mean' ;

	restore ;

};


clear;

foreach sample in uealllow ueallhigh  {;
	foreach method in rd iv {;
		use "`crime'/prworaexact_bw264_crime_poly1allcov_`method'_`sample'.dta" ;
 
 			#delim;

			gen n_crime2 = strpos(variables, "n_crime2") ;

			keep if n_crime2 == 1 | variables == "age18_yes" | variables == 		"n_age18unfav" | variables == "n_onssi1922" ;
								  
			if "`method'" == "rd" {;

				keep variables n_post sen_post N ;
			
			};
			
			else {;
			
				keep if n_crime2 == 1  ;  				
				keep variables n_age18unfav sen_age18unfav N;
			};

			replace variables = subinstr(variables,"_18_38", "", .) ;
			replace variables = subinstr(variables,"n_crime2", "", .) ;
			replace variables = subinstr(variables,"n_", "", .) ;

			#delim;

			if "`method'" == "rd" {;
			
			local crime "char genr ahinc shinc" ;

			};
			
			else {;
			
				local crime "char genr ahinc shinc" ;

			
			};
						 
			gen keep = strpos(variables, "char") ;

			foreach var in `crime' {;

				replace keep = strpos(variables, "`var'") if keep == 0;

			};

			drop if keep == 0 ;
			drop keep ;


			merge 1:1 variables using ``sample'_mean' ;
			drop _merge ;


			gen flag_int = strpos(variables, "t") ;

			gen length = length(variables) ;
			replace flag_int = 1 if variables == "shinc" | variables == "sipp" ;
			replace flag_int = 1 if flag_int != 0 & length == 5;
			replace flag_int = 0 if flag_int != 1 ;
			drop if strpos(variables,"_18_40") ;
			
			replace variables = "Any charge" if variables == "char" | variables == "chart" ;
			replace variables = "Income-generating charge" if variables == "genr" | variables == "genrt" ;  		
			replace variables = "Incarcerated (annual likelihood/days)" if variables == "ahinc" | variables == "shinc" ;

			sort variables flag_int ;

			if "`method'" == "rd" {;

				rename n_post pt_est_rd ;
				rename sen_post se_rd ;
				gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
				gen sta_sign_rd = "" ;
				replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
				replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
				replace sta_sign_rd = "***" if star_rd <= 0.01 ;
				drop star_rd ;
			};
			
			else {;
				
				rename n_age18unfav pt_est_iv ;
				rename sen_age18unfav se_iv ;
				gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
				gen sta_sign_iv = "" ;
				replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
				replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
				replace sta_sign_iv = "***" if star_iv <= 0.01 ;
				drop star_iv ;
			};
			
			
			
			order variables pt_est* se* sta_sign* mean N flag_int;
			
			
			tostring flag_int, replace ;
			
			replace flag_int = "_in" if flag_int == "1" ;
			replace flag_int = "_ex" if flag_int == "0" ;
			
			drop length ;
			
			reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;

			tostring se*, replace ;
			replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
			replace se_`method'_in = "(" + se_`method'_in + ")" ;
			
			tempfile all_`method'_`sample' ;
			save `all_`method'_`sample'' ;
			

	}; 
		
		use `all_rd_`sample'', clear ;
		
		merge 1:1 variables using `all_iv_`sample'' ;
		drop _merge ;
		
		
		order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex
						pt_est_rd_in sta_sign_rd_in se_rd_in pt_est_iv_in sta_sign_iv_in se_iv_in mean_in N_in ;
		
		
		gen order = 1 if variables == "Any charge" ;
		replace order = 2 if variables == "Income-generating charge" ;
		replace order = 3 if variables == "Incarcerated (annual likelihood/days)" ;
		
		tempfile compiled_all_`sample' ;
		save `compiled_all_`sample'' ;		
		
};


/* ***************************** */
/* **** EMPLOYMENY VARIABLE **** */
/* ***************************** */

use "`crime'/prwora_crime_summstats2.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_uealllow "ual" ;
local pre_ueallhigh "uah" ;

foreach sample in uealllow ueallhigh  {;

	preserve ;

		keep `pre_`sample''avg_kidemp15* ;

		xpose, clear varname ;

		rename v1 mean ;
		rename _varname variables ;
		order variables mean ;

		replace variables = subinstr(variables,"_1997_2017", "", .) ;
		replace variables = "avg_kidemp15" if variables == "`pre_`sample''avg_kidemp15" ; 

		tempfile `sample'_mean ;
		save ``sample'_mean', replace ;

	restore ;

};


clear;

foreach sample in uealllow ueallhigh  {;
	foreach method in rd iv {;
		use "`crime'/prwora_bw264_crime_poly1allcov_`method'_`sample'.dta" ;
 
			#delim;
			
			keep if variables == "avg_kidemp15_1997_2017" ;
								  
			if "`method'" == "rd" {;

				keep variables n_post sen_post N ;
			
			};
			
			else {;
			
				keep if variables == "avg_kidemp15_1997_2017" ;  				
				keep variables n_age18unfav sen_age18unfav N;
			};

			replace variables = subinstr(variables,"_1997_2017", "", .) ;
			replace variables = subinstr(variables,"n_", "", .) ;

			#delim;

			if "`method'" == "rd" {;
			
			local crime "avg_kidemp15" ;

			};
			
			else {;
			
				local crime "avg_kidemp15" ;

			
			};
						 
			gen keep = strpos(variables, "char") ;

			foreach var in `crime' {;

				replace keep = strpos(variables, "`var'") if keep == 0;

			};

			drop if keep == 0 ;
			drop keep ;


			merge 1:1 variables using ``sample'_mean' ;
			drop _merge ;


			gen flag_int = strpos(variables, "t") ;

			gen length = length(variables) ;
			replace flag_int = 1 if flag_int != 0 & length == 5;
			replace flag_int = 0 if flag_int != 1 ;
			
		    replace variables = "Annual employment (earnings > $15K/yr)" if variables == "avg_kidemp15" ;  

			sort variables flag_int ;

			if "`method'" == "rd" {;

				rename n_post pt_est_rd ;
				rename sen_post se_rd ;
				gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
				gen sta_sign_rd = "" ;
				replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
				replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
				replace sta_sign_rd = "***" if star_rd <= 0.01 ;
				drop star_rd ;
			};
			
			else {;
				
				rename n_age18unfav pt_est_iv ;
				rename sen_age18unfav se_iv ;
				gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
				gen sta_sign_iv = "" ;
				replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
				replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
				replace sta_sign_iv = "***" if star_iv <= 0.01 ;
				drop star_iv ;
			};
			
			
			
			order variables pt_est* se* sta_sign* mean N flag_int;
			
			
			tostring flag_int, replace ;
			
			replace flag_int = "_in" if flag_int == "1" ;
			replace flag_int = "_ex" if flag_int == "0" ;
			
			drop length ;
			
			reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;

			tostring se*, replace ;
			replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
			
			tempfile all_`method'_`sample' ;
			save `all_`method'_`sample'' ;
			

	}; 
		
		use `all_rd_`sample'', clear ;
		
		merge 1:1 variables using `all_iv_`sample'' ;
		drop _merge ;
		
		
		order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex	 ;
		
		
		gen order = 4 if variables == "Annual employment (earnings > $15K/yr)" ; 
		
		append using `compiled_all_`sample'' ;
		
		sort order ;
		
		drop order ;
		
		tempfile full_all_`sample' ;
		save `full_all_`sample'' ;		
		
};


/* *********************** */
/* **** ALL SUBGROUPS **** */
/* *********************** */
   
use `full_all_uealllow', clear ;
append using `full_all_ueallhigh' ;

insobs 1, before(1) ;
replace variables = "Low-unemployment counties" in 1 ;
insobs 1, before(6) ;
replace variables = "High-unemployment counties" in 6 ;

export excel using "`output'/tabB12.xls", firstrow(variables) ;
